﻿CREATE FUNCTION [dbo].[Collection_Borrowers](
	@UsixID varchar(8),
	@CompanyID int
)RETURNS TABLE 
RETURN 
SELECT CF_E.ApplID, L_B.Pos, CF_E.Name AS Name,
  ISNULL(CF_E.SSN, B0.SSN) AS SSN, 
  ISNULL(NULLIF (CF_E.Address, ''), A.Street) AS Street,
  ISNULL(NULLIF (CF_E.City, ''), A.CityName) AS CityName,
  ISNULL(NULLIF (LEFT(CF_E.[Zip Code],5), ''), A.Zip) AS Zip,
  ISNULL(NULLIF (CF_E.St, ''), A.StateCode) AS StateCode,
  REPLACE(ISNULL(NULLIF (CF_E.[Home Phone #], ''), B0.PhoneHome), '-', '') AS PhoneHome,
  REPLACE(ISNULL(NULLIF (CF_E.[Primary Cell#], ''), B0.PhoneCell), '-', '') AS PhoneCell, 
  REPLACE(ISNULL(NULLIF (CF_E.[Work Phone #], ''), E.Phone), '-', '') AS PhoneWork,
  ISNULL(L_B.BorrowerID,0)BorrowerID
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID AND L_B.Pos = 0 INNER JOIN
                      t_Address AS A ON B0.ID = A.BorrowerID AND A.Pos = 0 INNER JOIN
                      t_Employment AS E ON B0.ID = E.BorrowerID AND E.JobNumber = 0 AND E.Pos = 0 RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E WITH(nolock) ON L_B.LoanID = CF_E.ApplID
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID_Raw = @CompanyID)
UNION ALL
SELECT     CF_E.ApplID, ISNULL(L_B.Pos, 1) AS Expr1, CF_E.[Joint Name] AS Name,
	ISNULL(CF_E.[Joint SSN], B0.SSN) AS SSN,
	ISNULL(NULLIF (CF_E.[Joint Address], ''), A.Street) AS Street,
	ISNULL(NULLIF (CF_E.[Joint City], ''), A.CityName) AS CityName,
	ISNULL(NULLIF (LEFT(CF_E.[Joint Zipcode],5), ''), A.Zip0) AS Zip,
	ISNULL(NULLIF (CF_E.[Joint State], ''), A.StateCode) AS StateCode, 
	REPLACE(ISNULL(NULLIF (CF_E.[Joint H Phone#], ''), B0.PhoneHome), '-', '') AS PhoneHome,
	REPLACE(ISNULL(NULLIF (CF_E.[Joint Cell#], ''), B0.PhoneCell), '-', '') AS PhoneCell,
	REPLACE(ISNULL(NULLIF (CF_E.[Joint W Phone#], ''), E.Phone), '-', '') AS PhoneWork,
	ISNULL(L_B.BorrowerID,0)BorrowerID
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID AND L_B.Pos = 1 INNER JOIN
                      t_Address AS A ON B0.ID = A.BorrowerID AND A.Pos = 0 INNER JOIN
                      t_Employment AS E ON B0.ID = E.BorrowerID AND E.JobNumber = 0 AND E.Pos = 0 RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E WITH(nolock) ON L_B.LoanID = CF_E.ApplID
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID_Raw = @CompanyID) AND (ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name]) > '')
/*
SELECT     CF_E.ApplID,ISNULL(L_B.Pos,1), ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name]) AS Name, ISNULL(B0.SSN, CF_E.[Joint SSN]) AS SSN
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND (L_B.Pos = 1)
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name])>''

*/


--SELECT * FROM Collection_Borrowers('DA105794',2)



